<?php

class Excel_gen Extends MY_Controller
{
	private $column_letters = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ','BA','BB','BC','BD');
	public function __construct()
	{
		parent::__construct();
		$this->page_auth->secure_page('all');
		$this->load->library('excel');
		$this->load->model('M_excel_printables','mep');
		$this->load->library('token');
		$this->load->helper('misc');
	}
	
	public function index()
	{
		show_404();
	}
	
	public function student_profile_generator()
	{
		$this->load->model('M_levels','ml');
		$this->load->model('M_profile','mp');
		
		$this->view_data['column'] = $this->mp->get_profile_column();
		$this->view_data['level'] = $this->ml->get_all_levels_array(FALSE);
		$this->view_data['form_token'] = $this->token->get_token();
		
		if($this->input->post('generate_profiles'))
		{
			if($this->token->validate_token($this->input->post('sd_loc')))
			{
				$result = $this->mep->get_all_student_profiles($this->input->post('column'),$this->input->post('level'));

					$this->_generate_student_profile($result->result,$result->columns);
			}else{
				$this->token->destroy_token();
				$this->_msg('n','Token Expired. Try again.','excel_gen/student_profile_generator');
			}
		}else{
			$this->token->set_token();
		}
	}
	
	private function _generate_student_profile($values)
	{
		$column = array_keys((array)$values[0]);
		$column_count = count(array_keys((array)$values[0]));
		$value_count = count($values);
		//load our new PHPExcel library
		$this->load->library('excel');
		//activate worksheet number 1
		$this->excel->setActiveSheetIndex(0);
		//name the worksheet
		$this->excel->getActiveSheet()->setTitle('test worksheet');
		$objectSheet = $this->excel->getActiveSheet();
		ini_set('memory_limit','500M');
		ini_set('max_execution_time',60);
		
		
		
		$objectSheet->setTitle('Student Profiles');
		
		$objectSheet->setCellValue('A1','Student Profiles');
		
		//set spreadsheet headers
		foreach($column as $k => $col)
		{
			$objectSheet->setCellValue($this->_get_letter($k).'2',$col);
		}
		
		
		$cn = 3; //cellnumber
		foreach($values as $k => $v)
		{
			$fullname = ucwords($v->lastname.' '.$v->middlename.', '.$v->lastname);
			$objectSheet->setCellValue('A'.$cn, $fullname);
			foreach($column as $ck => $cv)
			{
				$objectSheet->setCellValue($this->_get_letter($ck).$cn, $v->$cv);
				$last_cell = $this->_get_letter($ck);
			}
			$cn++;
		}
		
		$objectSheet->getStyle('A1')->getFont()->setSize(20);
		$objectSheet->mergeCells('A1:F1');
		$objectSheet->getStyle("A2:{$last_cell}2")->applyFromArray(array("font" => array( "bold" => true)));
		$objectSheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
		$objectSheet->getStyle("A2:{$last_cell}2")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		
		@ob_end_clean();
		
		$filename='student_profiles_'.date('m_d_y_h-i-s',time()).'.xls'; //save our workbook as this file name
		header('Content-Type: application/vnd.ms-excel'); //mime type
		header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
		header('Cache-Control: max-age=0'); //no cache
		//save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)
		//if you want to save it as .XLSX Excel 2007 format				
		$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');  
		 @ob_end_clean();
		//force user to download the Excel file without writing it to server's HD
		$objWriter->save('php://output');
		@$objectSheet->disconnectWorksheets();
		unset($objectSheet);
		
	}
	
	
	public function generate_excel_student_payments_daily($year,$month,$day,$auth)
	{
		if($this->authlink->check_hash_make($year,$auth)
			AND check_year($year) AND check_month($month) 
			AND is_numeric($day))
		{
			$this->load->model('M_finance','mf');
			$data = $this->mf->monthly_daily_reports_with_names($year,$month,$day);
			
			//load our new PHPExcel library
			$this->load->library('excel');
			//activate worksheet number 1
			$this->excel->setActiveSheetIndex(0);
			//name the worksheet
			$this->excel->getActiveSheet()->setTitle('Student Monthly Payments');
			$objectSheet = $this->excel->getActiveSheet();
			ini_set('memory_limit','500M');
			ini_set('max_execution_time',60);
		
		
			$objectSheet->setTitle('Monthly Payments');
		
			$objectSheet->setCellValue('A1','Payments for the month of: '.date('F',mktime(0,0,0,$month,0,0)));
			$objectSheet->setCellValue('A2','Year: '.date('Y',mktime(0,0,0,0,0,$year)));
			$objectSheet->setCellValue('A3','Students Name');
			$objectSheet->setCellValue('B3','Amount Paid');
			$objectSheet->setCellValue('C3','Grade Level');
			
			$cell_number = 4;
			foreach($data as $k => $v)
			{
				$objectSheet->setCellValue('A'.$cell_number,$v->fullname);
				$objectSheet->setCellValue('B'.$cell_number,$v->amt_paid);
				$objectSheet->setCellValue('C'.$cell_number,$v->grade_level);
				$objectSheet->getStyle('B'.($cell_number))->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
				$cell_number++;
			}
		
			$objectSheet->getStyle('A1')->getFont()->setSize(15);
			$objectSheet->getStyle('A2')->getFont()->setSize(15);
			$objectSheet->mergeCells('A1:C1');
			$objectSheet->mergeCells('A2:C2');
			$objectSheet->getStyle("A1:C3")->applyFromArray(array("font" => array( "bold" => true)));
			// $objectSheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
			// $objectSheet->getStyle("C1:C3")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

			@ob_end_clean();
			
			$filename='student_payments_'.date('m_d_y_h-i-s',time()).'.xls'; //save our workbook as this file name
			header('Content-Type: application/vnd.ms-excel'); //mime type
			header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
			header('Cache-Control: max-age=0'); //no cache
			//save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)
			//if you want to save it as .XLSX Excel 2007 format				
			$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');  
			 @ob_end_clean();
			//force user to download the Excel file without writing it to server's HD
			$objWriter->save('php://output');
			@$objectSheet->disconnectWorksheets();
			unset($objectSheet);
		
		}else
		{

		}
	}
	
	public function yearly_reports($id = FALSE,$auth = FALSE)
	{
		if($id AND $this->authlink->check_hash_make($id,$auth))
		{
			$this->load->model('M_finance','mf');
			$pid = $this->hs->decrypt($id)[0];
			$result = $this->mf->yearly_schoolyear_reports($pid);
			pd($result->values);
			$this->load->library('excel');
			//activate worksheet number 1
			$this->excel->setActiveSheetIndex(0);
			//name the worksheet
			$this->excel->getActiveSheet()->setTitle('Student Monthly Payments');
			$objectSheet = $this->excel->getActiveSheet();
			ini_set('memory_limit','500M');
			ini_set('max_execution_time',60);
			
			
			$objectSheet->setTitle('Yearly Payment Report');
			
			if($result->status == 'all')
			{	
					foreach($result->values as $k => $v)
					{
				
					
					
					
					}
			}else
			{
				foreach($result->values as $k => $v)
				{
					
				}
			}
		}else{
			$this->_msg('e','Please refresh cache, Token for generating report is expired','finance/reports#yearly_reports');
		}
	}
	
	public function monthly_reports($params,$auth)
	{
		$this->load->model('M_finance');
		$r = $this->hs->decrypt($params);

		$view = $this->M_finance->daily_month_reports_with_names_for_report($r[0],$r[1]);
	
		
		pd($view);
		
		foreach($view as $key => $val)
		{
			// if(!empty($val)
		}
	}
	
	private function _get_letter($num)
	{
		$numeric = $num % 26;
		$letter = chr(65 + $numeric);
		$num2 = intval($num / 26);
		if ($num2 > 0) {
			return $this->_get_letter($num2 - 1) . $letter;
		} else {
			return $letter;
		}
	}
	
	public function balancereports($hash = '')
	{
		$x = $this->hs->decrypt($hash);
		if($x)
		{
			$this->load->model('M_fees','mf');
			$data = $this->mf->get_balance_report($x[0]);

			$csv = 'LAST NAME,FIRST NAME,GRADE LEVEL,GENDER,BALANCE'.PHP_EOL;
			foreach($data as $v)
			{
				$totals[] = $v['remaining_balance'];
				$csv .= "{$v['fullname']},{$v['grade_level']},{$v['gender']},{$v['remaining_balance']}".PHP_EOL;
			}
			$csv .= ",,,,".array_sum($totals)."\n";

			$this->load->helper('file');
			$filename = 'balancereports'.time();
				//write tp PHP memory
			write_file('php://output', $csv);

			//set headers
			header("Content-disposition: attachment; filename={$filename}.csv");
			header("Content-Type: text/csv");
			//output
			read_file('php://output');
			//end
			exit();
		}

		show_error('Unable to retrieve the requested file.',404);
	}
	
	public function print_birthday($monthly='', $yearly='')
	{	
		if($monthly)
		{
			$yearly = $yearly == '' ? date('Y',time()) : $yearly;
			
			$this->load->model('M_search');
			$result = $this->M_search->get_birthdays_csv($yearly,$monthly);

			if($result){
			
				$csv = 'LAST NAME,FIRST NAME,GRADE LEVEL,GENDER,BIRTHDAY'.PHP_EOL;
				foreach($result as $v)
				{
					$csv .= ucwords(strtolower("{$v->lastname}")).",".ucwords(strtolower("{$v->firstname}")).",".ucwords(strtolower("{$v->level_code}")).",".ucwords(strtolower("{$v->gender}")).",".ucwords(strtolower("{$v->day_of_birth}"))."".PHP_EOL;
				}
			
				$this->load->helper('file');
				$filename = ''.$monthly.'_birthdays';

				//write to PHP memory
				write_file('php://output', $csv);

				//set headers
				header("Content-disposition: attachment; filename={$filename}.csv");
				header("Content-Type: text/csv");
				
				//output and download
				read_file('php://output');

				//stop any unwanted things from happening in the world
				exit();
			}else{
				show_error('Unable to find selected file. No records to export.',404);
			}
			
		}else{
			show_404();
		}
	}
	
	public function siblings($hash = '')
	{
		$x = $this->hs->decrypt($hash);
		if($x)
		{
			$filename='siblings_'.date('m_d_y_h-i-s',time()).'.xls'; //save our workbook as this file name

			$this->load->model('M_search','ms');
			$siblings = $this->ms->get_siblings_all();

			//load our new PHPExcel library
			$this->load->library('excel');
			//activate worksheet number 1
			$this->excel->setActiveSheetIndex(0);
			
			// $this->excel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);
			// $this->excel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
			// $this->excel->getActiveSheet()->getPageSetup()->setFitToPage(true);
			// $this->excel->getActiveSheet()->getPageSetup()->setFitToWidth(40);
			// $this->excel->getActiveSheet()->getPageSetup()->setFitToHeight(0);

			$objectSheet = $this->excel->getActiveSheet();
			ini_set('memory_limit','500M');
			ini_set('max_execution_time',60);
			set_time_limit(0);


			$pageMargins = $objectSheet->getPageMargins();
			// margin is set in inches (0.5cm)
			$margin = 0.5 / 2.54;
			$pageMargins->setTop($margin);
			$pageMargins->setBottom($margin);
			$pageMargins->setLeft($margin);
			$pageMargins->setRight($margin);


			$objectSheet->setTitle('Student Siblings');

			// $objectSheet->setBreak('A30', PHPExcel_Worksheet::BREAK_ROW);
			// $objectSheet->setBreak('F30', PHPExcel_Worksheet::BREAK_COLUMN ); 

			$objectSheet->setShowGridlines(true); 
			$objectSheet->setPrintGridlines(true); 
			
			$total_count = 0;

			if($siblings)
			{
				$objectSheet->setCellValue('A1',$this->school_name);
				$objectSheet->setCellValue('A2',$this->school_address);
				$objectSheet->setCellValue('A3','PARENTS ORIENTATION');
				$objectSheet->setCellValue('B5','Grade Level');
				$objectSheet->setCellValue('C5','Father');
				$objectSheet->setCellValue('E5','Mother');
				$objectSheet->mergeCells('A1:F1');
				$objectSheet->mergeCells('A2:F2');
				$objectSheet->mergeCells('A3:F3');
				$objectSheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
				$objectSheet->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
				$objectSheet->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
				$objectSheet->getStyle('A5:F5')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
				$objectSheet->getStyle('C3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
				$objectSheet->getColumnDimension('A')->setWidth(20);
				$objectSheet->getColumnDimension('B')->setWidth(18);
				$objectSheet->getColumnDimension('C')->setWidth(22);
				$objectSheet->getColumnDimension('D')->setWidth(10);
				$objectSheet->getColumnDimension('E')->setWidth(22);
				$objectSheet->getColumnDimension('F')->setWidth(10);

				$cell_number = 5;
				$array = [];

				foreach($siblings as $lastname => $val)
				{
					if($val)
					{
						foreach ($val as $data) 
						{
							foreach ($data as $fathername => $children) 
							{
								//if the same fathername then same column else add extra cell as border
								$new_family = end($array) == $fathername ? 0 : 1;
								$array[] = $fathername;
								$cell_number += $new_family;

								$objectSheet->setCellValue('E'.$cell_number,ucwords(strtolower($children['mother'])));
								$objectSheet->setCellValue('C'.$cell_number,ucwords(strtolower($fathername)));
								$objectSheet->getStyle('E'.$cell_number)->getAlignment()->setWrapText(true);
								$objectSheet->getStyle('C'.$cell_number)->getAlignment()->setWrapText(true);
	
								if($children)
								{
									//total children
									$total = count($children['children']);
									$cell_number += $total;
									$child_count = 0;

									foreach ($children['children'] as $ckey => $cval) 
									{
										$total_count++;
										$current = ((($cell_number + $child_count) - $total));
										$objectSheet->setCellValue('A'.$current,$cval);
										$objectSheet->setCellValue('B'.$current,$children['gradelevel'][$ckey]);
										$objectSheet->getStyle('A'.$current)->getAlignment()->setWrapText(true);
										$objectSheet->getStyle('B'.$current)->getAlignment()->setWrapText(true);
										$child_count++;
									}
								}
							}
						}
					}
				}

				$objectSheet->setCellValue('A5','CHILDREN ('.$total_count.')');
				$objectSheet->getStyle('A6:F'.$total_count+2)->getAlignment()->setWrapText(true); 
			}else{
				show_404();
			}


			//===================================================================================
			@ob_end_clean();
			header('Content-Type: application/vnd.ms-excel'); //mime type
			header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
			header('Cache-Control: max-age=0'); //no cache
			//save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)
			//if you want to save it as .XLSX Excel 2007 format				
			$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');  
			 @ob_end_clean();
			//force user to download the Excel file without writing it to server's HD
			$objWriter->save('php://output');
			@$objectSheet->disconnectWorksheets();
			unset($objectSheet);
			//===================================================================================
		}else{
			show_404();
		}
	}
	
	public function insurance($hash = '')
	{
		$x = $this->hs->decrypt($hash);

		if($x)
		{
			$filename='insurance_'.date('m_d_y_h-i-s',time()).'.xls'; //save our workbook as this file name
			$this->load->model('M_student','ms');
			//load our new PHPExcel library
			$this->load->library('excel');
			//activate worksheet number 1
			$this->excel->setActiveSheetIndex(0);
			
			// $this->excel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);
			// $this->excel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
			// $this->excel->getActiveSheet()->getPageSetup()->setFitToPage(true);
			// $this->excel->getActiveSheet()->getPageSetup()->setFitToWidth(40);
			// $this->excel->getActiveSheet()->getPageSetup()->setFitToHeight(0);

			$objectSheet = $this->excel->getActiveSheet();
			ini_set('memory_limit','500M');
			ini_set('max_execution_time',60);
			set_time_limit(0);


			$res = $this->ms->for_insurance();

			$objectSheet->setCellValue('A1',$this->school_name);
			$objectSheet->setCellValue('A2',$this->school_address);
			$objectSheet->setCellValue('A3','');
			$objectSheet->setCellValue('A4','SY');
			$objectSheet->setCellValue('B4','Student\'s Fullname');
			$objectSheet->setCellValue('C4','Address');
			$objectSheet->setCellValue('D4','Student #');
			$objectSheet->setCellValue('E4','Cell #');
			$objectSheet->setCellValue('F4','Level');
			$objectSheet->setCellValue('G4','Gender');
			$objectSheet->setCellValue('H4','Birthdate');
			$objectSheet->setCellValue('I4','Father');
			$objectSheet->setCellValue('J4','Mother');
			$objectSheet->getStyle("A2:J4")->applyFromArray(array("font" => array( "bold" => true)));
			$objectSheet->mergeCells('A1:J1');
			$objectSheet->mergeCells('A2:J2');
			$objectSheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			$objectSheet->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			$objectSheet->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			$objectSheet->getStyle('A4:J4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			$objectSheet->getStyle('C3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			$objectSheet->getColumnDimension('A')->setWidth(20);
			$objectSheet->getColumnDimension('B')->setWidth(18);
			$objectSheet->getColumnDimension('C')->setWidth(22);
			$objectSheet->getColumnDimension('D')->setWidth(10);
			$objectSheet->getColumnDimension('E')->setWidth(22);
			$objectSheet->getColumnDimension('F')->setWidth(10);

			$cell_number = 5;
			foreach($res as $k => $v)
			{
				$contact = '';
				$contact .= $v->contact == '' ? '' : $v->contact.', ';
				$contact .= $v->mcontact == '' ? '' : $v->mcontact.', ';
				$contact .= $v->fcontact == '' ? '' : $v->fcontact.', ';
				$contact .= $v->foffice == '' ? '' : $v->foffice.', ';
				$contact .= $v->moffice == '' ? '' : $v->moffice;

				$objectSheet->setCellValue('A'.$cell_number,$this->schoolyear_date);
				$objectSheet->setCellValue('B'.$cell_number,ucwords(strtolower($v->fullname)));
				$objectSheet->setCellValue('C'.$cell_number,$v->address);
				$objectSheet->setCellValue('D'.$cell_number,$v->studNo);
				$objectSheet->setCellValue('E'.$cell_number,trim($contact));
				$objectSheet->setCellValue('F'.$cell_number,$v->level_desc);
				$objectSheet->setCellValue('G'.$cell_number,$v->gender);
				$objectSheet->setCellValue('H'.$cell_number,$v->bdate);
				$objectSheet->setCellValue('I'.$cell_number,ucwords(strtolower($v->father)));
				$objectSheet->setCellValue('J'.$cell_number,ucwords(strtolower($v->mother)));
				$cell_number++;
			}

			@ob_end_clean();
			header('Content-Type: application/vnd.ms-excel'); //mime type
			header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
			header('Cache-Control: max-age=0'); //no cache
			//save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)
			//if you want to save it as .XLSX Excel 2007 format				
			$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');  
			 @ob_end_clean();
			//force user to download the Excel file without writing it to server's HD
			$objWriter->save('php://output');
			@$objectSheet->disconnectWorksheets();
			unset($objectSheet);
		}

		show_404();
	}
	
	public function enrollmentlist($hash = '')
	{
		$x = $this->hs->decrypt($hash);

		if($x)
		{
			$filename='enrollmentlist_'.date('m_d_y_h-i-s',time()).'.xls'; //save our workbook as this file name
			$this->load->model('M_search','ms');
			//load our new PHPExcel library
			$this->load->library('excel');
			//activate worksheet number 1
			$this->excel->setActiveSheetIndex(0);

			$objectSheet = $this->excel->getActiveSheet();
			ini_set('memory_limit','500M');
			ini_set('max_execution_time',60);
			set_time_limit(0);

			$students =  $this->ms->verified_enrollees_for_enrollmentlist();

			$objectSheet->setCellValue('A1',$this->school_name);
			$objectSheet->setCellValue('A2',$this->school_address);
			$objectSheet->setCellValue('A3','');
			$objectSheet->setCellValue('A4','ID');
			$objectSheet->setCellValue('B4','Level & Section');
			$objectSheet->setCellValue('C4','Student Name');
			$objectSheet->setCellValue('D4','Gender');
			$objectSheet->setCellValue('E4','Birthdate');
			$objectSheet->setCellValue('F4','Age');
			$objectSheet->setCellValue('G4','Level');
			$objectSheet->setCellValue('H4','Nationality');

			$objectSheet->getStyle("A2:H4")->applyFromArray(array("font" => array( "bold" => true)));
			$objectSheet->mergeCells('A1:H1');
			$objectSheet->mergeCells('A2:H2');
			$objectSheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			$objectSheet->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			$objectSheet->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			$objectSheet->getStyle('A4:H4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			$objectSheet->getStyle('C3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			$objectSheet->getColumnDimension('A')->setWidth(20);
			$objectSheet->getColumnDimension('B')->setWidth(18);
			$objectSheet->getColumnDimension('C')->setWidth(22);
			$objectSheet->getColumnDimension('D')->setWidth(10);
			$objectSheet->getColumnDimension('E')->setWidth(22);
			$objectSheet->getColumnDimension('F')->setWidth(10);
			$objectSheet->getColumnDimension('G')->setWidth(10);
			$objectSheet->getColumnDimension('H')->setWidth(10);


			$status = 0;
			$cell = 5;
			$male = [];
			$female = [];
			$gmale = [];
			$gfemale = [];

			foreach($students as $v)
			{
				//pd($students);
				$levels[] = $v->level_desc;
				$stat = $v->registrar_approved == 1 ? '' : '*';
				$male[$v->level_desc][] = $v->gender == 'male' ? 1 : 0;
				$female[$v->level_desc][] = $v->gender == 'female' ? 1 : 0;
				$gmale[] = $v->gender == 'male' ? 1 : 0;
				$gfemale[] = $v->gender == 'female' ? 1 : 0;
				
				$objectSheet->setCellValue('A'.$cell,strtolower($v->idno));
				$objectSheet->setCellValue('B'.$cell,ucwords(strtolower($v->level_desc.' - '.$v->section_name)));
				$objectSheet->setCellValue('C'.$cell,ucwords(strtolower($v->fullname)).$stat);
				$objectSheet->setCellValue('D'.$cell,ucwords(strtolower($v->gender)));
				$objectSheet->setCellValue('E'.$cell,$v->dob);
				$objectSheet->setCellValue('F'.$cell,age($v->bdate,'2014-12-01'));
				$objectSheet->setCellValue('G'.$cell,ucwords(strtolower($v->level_code)));
				$objectSheet->setCellValue('H'.$cell,ucwords(strtolower($v->nationality)));

				if($status !== 0)
				{
					$lvl = $levels[count($levels)-2];
					if($lvl !== $v->level_desc)
					{
						$test=  ucwords(strtolower($lvl));
						$tmale = @array_sum($male[$lvl]);
						$tfemale = @array_sum($female[$lvl]);
						$ttotal = $tmale + $tfemale;
						$tvalue = "Male: {$tmale}, Female: {$tfemale} , Total: {$ttotal}";
						$objectSheet->setCellValue('A'.$cell,$tvalue);
						$objectSheet->mergeCells("A{$cell}:H{$cell}");
						$objectSheet->getStyle("A{$cell}")->applyFromArray(array("font" => array( "bold" => true)));
						$objectSheet->getStyle("A{$cell}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
					}
				}
				$status = 1;
				$cell++;
			}
			// pd($cell);
			$lvl = $levels[count($levels)-2];
			$test=  ucwords(strtolower($lvl));
			$tmale = @array_sum(array_values($male[$lvl]));
			$tfemale = @array_sum(array_values($female[$lvl]));
			$ttotal = $tmale + $tfemale;

			$tvalue = "Male: {$tmale}, Female: {$tfemale} , Total: {$ttotal}";
			$objectSheet->setCellValue('A'.$cell,$tvalue);
			$objectSheet->mergeCells("A{$cell}:H{$cell}");
			$objectSheet->getStyle("A{$cell}")->applyFromArray(array("font" => array( "bold" => true)));
			$objectSheet->getStyle("A{$cell}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

			$cell += 2;
			$tgfemale = @array_sum($gfemale);
			$tgmale = @array_sum($gmale);
			$gttotal = $tgfemale + $tgmale;
			$tvalue = "Male: {$tgmale}, Female: {$tgfemale} , Total: {$gttotal}";
			$objectSheet->setCellValue('A'.$cell,$tvalue);
			$objectSheet->mergeCells("A{$cell}:H{$cell}");
			$objectSheet->getStyle("A{$cell}")->applyFromArray(array("font" => array( "bold" => true)));
			$objectSheet->getStyle("A{$cell}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);			


			// echo '<pre>';
			// print_r($students);
			// exit();

			@ob_end_clean();
			header('Content-Type: application/vnd.ms-excel'); //mime type
			header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
			header('Cache-Control: max-age=0'); //no cache
			//save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)
			//if you want to save it as .XLSX Excel 2007 format				
			$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');  
			 @ob_end_clean();
			//force user to download the Excel file without writing it to server's HD
			$objWriter->save('php://output');
			@$objectSheet->disconnectWorksheets();
			unset($objectSheet);
		}
		show_404();
		exit();
	}
	
	public function custombdaylist($hash = '')
	{
		$x = $this->hs->decrypt($hash);


		$get = array_map(function($x){
			if($x !== FALSE)
			{
				$date = date_create_from_format('d/m/Y',$x);
				if($date !== FALSE){
					return date_format($date,'Y-m-d');
				}
			}
		},$this->input->get('datecolumn'));

		if($x)
		{
			$sec = $x[0];

			//save our workbook as this file name
			$filename='birthdates'.date('m_d_y_h-i-s',time()).'.xls';
			$this->load->model('M_search','ms');
			//load our new PHPExcel library
			$this->load->library('excel');
			//activate worksheet number 1
			$this->excel->setActiveSheetIndex(0);

			$objectSheet = $this->excel->getActiveSheet();
			ini_set('memory_limit','500M');
			ini_set('max_execution_time',60);
			set_time_limit(0);

			$this->load->model('M_student');
			$student_with_bdate = $this->M_student->get_student_with_bday();
			// vd($student_with_bdate);

			$objectSheet->setCellValue('A1',$this->school_name);
			$objectSheet->setCellValue('A2',$this->school_address);
			$objectSheet->setCellValue('E3','Age On');
			$objectSheet->setCellValue('A4','Students Name');
			$objectSheet->setCellValue('B4','Gender');
			$objectSheet->setCellValue('C4','Grade Level');
			$objectSheet->setCellValue('D4','Date of Birth');

			
			
			$letternum = 4;
			foreach ($get as $date){
				if($date !== NULL){
					$letter = $this->_get_letter($letternum);
					$objectSheet->setCellValue("{$letter}4",$date);
					$letternum++;
				}
			}
			$letternum = 4;
			$final_letter = $this->_get_letter($letternum);
			
			$objectSheet->mergeCells("A1:{$final_letter}1");
			$objectSheet->mergeCells("A2:{$final_letter}2");
			$objectSheet->mergeCells("E3:{$final_letter}3");

			$objectSheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			$objectSheet->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			$objectSheet->getStyle('E3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

			$data_row_start = 5;
			foreach($student_with_bdate as $v)
			{
				$objectSheet->setCellValue("A{$data_row_start}",ucwords(strtolower($v->fullname)));
				$objectSheet->setCellValue("B{$data_row_start}",$v->gender);
				$objectSheet->setCellValue("C{$data_row_start}",$v->level_desc);
				$objectSheet->setCellValue("D{$data_row_start}",$v->dob_format);

				$ln = 4;
				foreach ($get as $date){
					if($date !== NULL){
						$char = $this->_get_letter($ln);
						$objectSheet->setCellValue("{$char}{$data_row_start}",age($v->dob,$date));
						$ln++;
					}
				}

				$data_row_start++;
			}


			@ob_end_clean();
			header('Content-Type: application/vnd.ms-excel'); //mime type
			header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
			header('Cache-Control: max-age=0'); //no cache
			//save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)
			//if you want to save it as .XLSX Excel 2007 format				
			$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');  
			 @ob_end_clean();
			//force user to download the Excel file without writing it to server's HD
			$objWriter->save('php://output');
			@$objectSheet->disconnectWorksheets();
			unset($objectSheet);
		}
	}
	
	public function export($export_type=FALSE,$hashlink='')
	{
		if(in_array(strtolower($export_type),['gmail','yahoo']) AND $this->authlink->check_hash_make('keyToExportCSV',$hashlink))
		{
			$this->load->model('M_search');
			$result = $this->M_search->get_csv($export_type);

			if($result){
				$this->load->helper('file');
				$filename = $export_type.'_contacts_'.time();

				//write to PHP memory
				write_file('php://output', $result);

				//set headers
				header("Content-disposition: attachment; filename={$filename}.csv");
				header("Content-Type: text/csv");
				
				//output and download
				read_file('php://output');

				//stop any unwanted things from happening in the world
				exit();
			}else{
				show_error('Unable to find Selected File.',404);
			}

		}else{
			show_404();
		}

	}
}